/**
 * 
 */
package com.feib.stms.dao.jdbc;


import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.BusinessDayDao;
import com.feib.stms.dao.jdbc.rowmapper.BusinessDayRowMapper;
import com.feib.stms.model.BusinessDay;
import com.feib.stms.util.UserUtil;

/**
 * @author Lion Gao
 *
 */
@Repository("businessDayDao")
public class BusinessDayJdbcDao extends BaseJdbcDao implements BusinessDayDao
{	
	private SimpleJdbcInsert businessDayJdbcInsert;
	
	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.businessDayJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.businessDayJdbcInsert.withTableName("BUSINESS_DAY")
			.usingColumns("THE_DATE", "STATUS", "UPDATED_DATE", "UPDATED_BY");
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.BusinessDayDao#getBusinessDayByDate(java.lang.String, java.lang.String)
	 */
	public List<BusinessDay> getBusinessDayByDate(String year, String month) {
		String temp = year + "/" + month;
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		
		sql.append("SELECT WD.*");
		sql.append(" FROM BUSINESS_DAY WD WHERE SUBSTR(TO_CHAR(WD.THE_DATE,'yyyy/MM/dd'),0,7) = :THE_DATE");
		parameters.put("THE_DATE",  temp);
		sql.append(" ORDER BY TO_CHAR(WD.THE_DATE,'yyyy/MM/dd')");
		
		
		
		return this.getSimpleJdbcTemplate().query(sql.toString(), new BusinessDayRowMapper(), parameters);
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.BusinessDayDao#delete(java.lang.String , java.lang.String)
	 */
	public int delete(String year, String month) {
		String temp = year + "/" + month;
		if(getBusinessDayByDate(year , month) == null || (getBusinessDayByDate(year , month) != null && getBusinessDayByDate(year , month).size() <= 0) ){
			return 1;
		}
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("DELETE FROM BUSINESS_DAY WHERE SUBSTR(TO_CHAR(THE_DATE,'yyyy/MM/dd'),0,7) = :THE_DATE");
		parameters.addValue("THE_DATE", temp, Types.VARCHAR);
		int updateCount = this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
		return updateCount;
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.BusinessDayDao#insert(com.feib.stms.model.BusinessDay)
	 */
	public int insert(BusinessDay businessDay) {
		int insertCounts = 0;
		try{
			if(businessDay != null){
				businessDay.setUpdatedBy(UserUtil.getUserGroup().getGroupNo() + "_" + UserUtil.getUserId());
				businessDay.setUpdatedDate(new Date());
			}
			
			MapSqlParameterSource parameters = new MapSqlParameterSource();
			parameters.addValue("THE_DATE",  businessDay.getTheDate(), Types.TIMESTAMP);
			parameters.addValue("STATUS",  businessDay.getStatus(), Types.VARCHAR);
			parameters.addValue("UPDATED_DATE",  businessDay.getUpdatedDate(), Types.TIMESTAMP);
			parameters.addValue("UPDATED_BY",  businessDay.getUpdatedBy(), Types.VARCHAR);
						
			insertCounts = businessDayJdbcInsert.execute(parameters);
			
			if (insertCounts > 0)
				businessDay.setTheDate(businessDay.getTheDate());
			return insertCounts;
			
		}
		catch (DataAccessException e) {
			logger.error(e.getMessage(), e);
		}
		return 0;
	}
	
	public Date getNextBusinessDay(Date day){
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("SELECT min(THE_DATE) as THE_DATE");
		sql.append(" FROM BUSINESS_DAY WHERE THE_DATE > :THE_DATE AND STATUS = 'W'");
		parameters.addValue("THE_DATE", day, Types.TIMESTAMP);
		
		BusinessDay b = DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new BusinessDayRowMapper(), parameters));
		
		return (null != b ? b.getTheDate() : null);
	}

}
